import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/question.dart';
import '../models/answer.dart';

class StorageService {
  static Database? _database;
  static const int _databaseVersion = 2; // 增加版本号以触发数据库升级

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    final path = join(await getDatabasesPath(), 'driver_exam.db');

    // 打开数据库并处理版本升级
    return openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  // 创建数据库表
  Future<void> _onCreate(Database db, int version) async {
    // 创建问题表
    await db.execute('''
      CREATE TABLE questions(
        id INTEGER PRIMARY KEY,
        title TEXT,
        rank INTEGER,
        type INTEGER,
        op1 TEXT,
        op2 TEXT,
        op3 TEXT,
        op4 TEXT,
        titleType INTEGER,
        titlePic TEXT
      )
    ''');

    // 创建答案表
    await db.execute('''
      CREATE TABLE answers(
        id INTEGER PRIMARY KEY,
        explain TEXT,
        answer TEXT
      )
    ''');

    // 创建错题表
    await db.execute('''
      CREATE TABLE wrong_questions(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        question_id INTEGER,
        rank INTEGER,
        type INTEGER,
        created_at INTEGER,
        FOREIGN KEY (question_id) REFERENCES questions(id)
      )
    ''');

    print("数据库表创建完成");
  }

  // 处理数据库升级
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    print("数据库升级: $oldVersion -> $newVersion");

    // 如果旧版本小于2且当前版本大于等于2，则创建错题表
    if (oldVersion < 2 && newVersion >= 2) {
      // 检查wrong_questions表是否已存在
      final tables = await db.rawQuery(
          "SELECT name FROM sqlite_master WHERE type='table' AND name='wrong_questions'");
      if (tables.isEmpty) {
        await db.execute('''
          CREATE TABLE wrong_questions(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question_id INTEGER,
            rank INTEGER,
            type INTEGER,
            created_at INTEGER,
            FOREIGN KEY (question_id) REFERENCES questions(id)
          )
        ''');
        print("错题表创建完成");
      }
    }
  }

  // Save questions to local database
  Future<void> saveQuestions(List<Question> questions) async {
    final db = await database;
    final batch = db.batch();

    for (final question in questions) {
      batch.insert(
        'questions',
        {
          'id': question.id,
          'title': question.title,
          'rank': question.rank,
          'type': question.type,
          'op1': question.op1,
          'op2': question.op2,
          'op3': question.op3,
          'op4': question.op4,
          'titleType': question.titleType,
          'titlePic': question.titlePic,
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }

    await batch.commit();
  }

  // Get cached questions
  Future<List<Question>> getCachedQuestions(int rank, int type) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'questions',
      where: 'rank = ? AND type = ?',
      whereArgs: [rank, type],
    );

    return List.generate(maps.length, (i) => Question.fromJson(maps[i]));
  }

  // Similar methods for answers
  // ...

  // 添加调试日志的添加错题方法
  Future<void> addWrongQuestion(Question question) async {
    final db = await database;

    print(
        "开始添加错题: ID=${question.id}, 类型=${question.type}, 车型=${question.rank}");

    try {
      // 先确保问题保存到问题表
      await db.insert(
        'questions',
        {
          'id': question.id,
          'title': question.title,
          'rank': question.rank,
          'type': question.type,
          'op1': question.op1,
          'op2': question.op2,
          'op3': question.op3,
          'op4': question.op4,
          'titleType': question.titleType,
          'titlePic': question.titlePic,
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );

      print("问题保存成功");

      // 检查错题是否已存在
      final existing = await db.query(
        'wrong_questions',
        where: 'question_id = ?',
        whereArgs: [question.id],
      );

      if (existing.isNotEmpty) {
        print("错题已存在，无需重复添加");
        return;
      }

      // 添加到错题表
      final id = await db.insert(
        'wrong_questions',
        {
          'question_id': question.id,
          'rank': question.rank,
          'type': question.type,
          'created_at': DateTime.now().millisecondsSinceEpoch,
        },
      );

      print("错题添加成功，ID=$id");

      // 验证是否添加成功
      final check = await db.query(
        'wrong_questions',
        where: 'question_id = ?',
        whereArgs: [question.id],
      );

      print("验证结果: ${check.isNotEmpty ? '成功' : '失败'}");
    } catch (e) {
      print("添加错题失败: $e");
      rethrow;
    }
  }

  // 删除错题
  Future<void> removeWrongQuestion(int questionId) async {
    final db = await database;
    await db.delete(
      'wrong_questions',
      where: 'question_id = ?',
      whereArgs: [questionId],
    );
  }

  // 检查问题是否在错题集中
  Future<bool> isWrongQuestion(int questionId) async {
    final db = await database;
    final result = await db.query(
      'wrong_questions',
      where: 'question_id = ?',
      whereArgs: [questionId],
    );
    return result.isNotEmpty;
  }

  // 获取错题列表，添加调试日志
  Future<List<Question>> getWrongQuestions(int rank, int type) async {
    final db = await database;

    print("开始获取错题列表: 车型=$rank, 科目=$type");

    try {
      // 先检查wrong_questions表是否有数据
      final wrongCount = Sqflite.firstIntValue(await db.rawQuery(
              'SELECT COUNT(*) FROM wrong_questions WHERE rank = ? AND type = ?',
              [rank, type])) ??
          0;
      print("错题表中符合条件的记录数: $wrongCount");

      if (wrongCount == 0) {
        return [];
      }

      // 联表查询获取错题
      final List<Map<String, dynamic>> maps = await db.rawQuery('''
        SELECT q.* FROM questions q
        INNER JOIN wrong_questions w ON q.id = w.question_id
        WHERE w.rank = ? AND w.type = ?
        ORDER BY w.created_at DESC
      ''', [rank, type]);

      print("查询到的错题数量: ${maps.length}");

      // 检查查询结果
      if (maps.isNotEmpty) {
        print("第一条错题: ${maps.first}");
      }

      return List.generate(maps.length, (i) => Question.fromJson(maps[i]));
    } catch (e) {
      print("获取错题列表失败: $e");
      rethrow;
    }
  }

  // 获取所有错题统计
  Future<Map<String, int>> getWrongQuestionStats() async {
    final db = await database;

    final List<Map<String, dynamic>> maps = await db.rawQuery('''
      SELECT rank, type, COUNT(*) as count
      FROM wrong_questions
      GROUP BY rank, type
    ''');

    Map<String, int> stats = {};
    for (var map in maps) {
      final key = '${map['rank']}_${map['type']}';
      stats[key] = map['count'];
    }

    return stats;
  }
}
